<?php

declare(strict_types=1);

namespace App\Utils;


use Hyperf\HttpServer\Contract\ResponseInterface;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Excel
{

    /**
     * @param string $name 导出的文件名
     * @param array $data 要导出的数据，是一个二维数组
     * @param array $head 表头，一维数组
     * @param array $dataType 数据类型，一维数组，对应$data里的数据 [PhpOffice\PhpSpreadsheet\Cell\DataType:TYPE_STRING,DataType:TYPE_NUMERIC,DataType:TYPE_FORMULA]
     * @return \Psr\Http\Message\ResponseInterface
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function export(string $name = "", array $data = [], array $head = [], array $dataType = [])
    {
        $documentRoot = config('server.settings.document_root');

        $width = 20;
        $spreadsheet = new Spreadsheet();

//        $spreadsheet->getActiveSheet()->setTitle(''); //sheet名称
        $sheet = $spreadsheet->getActiveSheet();

        //当前行
        $indexRow = 1;

        //设置头
        if (count($head) > 0) {
            $key = ord('A');
            $key2 = ord('@');
            foreach ($head as $v) {
                $renderColumn = self::renderColumn($key, $key2);
                $key = $renderColumn['key'];
                $key2 = $renderColumn['key2'];
                $column = $renderColumn['column'];

                $sheet->setCellValue($column . $indexRow, $v);
                $spreadsheet->getActiveSheet()->getColumnDimension($column)->setWidth($width);
            }
            $indexRow++;
        }

        //数据
        foreach ($data as $k => $rows) {
            $key = ord('A');
            $key2 = ord('@');
            $index = 0;
            foreach ($rows as $value) {
                $renderColumn = self::renderColumn($key, $key2);
                $key = $renderColumn['key'];
                $key2 = $renderColumn['key2'];
                $column = $renderColumn['column'];

                if (isset($dataType[$index])) {
                    //设置数据类型
                    $sheet->setCellValueExplicit($column . $indexRow, $value, $dataType[$index]);
                } else {
                    $sheet->setCellValue($column . $indexRow, $value);
                }
                $spreadsheet->getActiveSheet()->getColumnDimension($column)->setWidth($width);
                $index++;
            }
            $indexRow++;
        }


        $fileDir = $documentRoot . '/tmp';
        if (!is_dir($fileDir)) {
            mkdir($fileDir, 0777, true);
        }

        list($usec, $sec) = explode(" ", microtime());
        $usec = $usec * 1000000;
        $filePath = "{$fileDir}/{$name}_{$sec}{$usec}.xlsx";

        $writer = new Xlsx($spreadsheet);
        $writer->save($filePath);

        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);

        $handler = fopen($filePath, 'r+b');
        $fileSize = filesize($filePath);
        $data = fread($handler, $fileSize);
        fclose($handler);

        unlink($filePath);

        $name = $name . '_' . $sec;

        $response = di(ResponseInterface::class);
        return $response->raw($data)
            ->withHeader('Content-Type', 'application/vnd.ms-excel')
            ->withHeader('Content-Disposition', "attachment;filename={$name}.xlsx")
            ->withHeader('Cache-Control', 'max-age=0');
    }

    protected static function renderColumn(int $key, int $key2)
    {
        if ($key > ord('Z')) {
            $key2++;
            $key = ord('A');
            $column = chr($key2) . chr($key);
        } else {
            if ($key2 >= ord('A')) {
                $column = chr($key2) . chr($key);
            } else {
                $column = chr($key);
            }
        }
        $key++;
        return [
            'key' => $key,
            'key2' => $key2,
            'column' => $column
        ];
    }

    public function read($readerType, $filename, $columns)
    {
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($readerType);
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($filename); //载入excel表格

        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数
        $highestColumn = $worksheet->getHighestColumn(); // 总列数
        $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString(
            $highestColumn
        ); // e.g. 5

        $lines = $highestRow - 2;
        if ($lines <= 0) {
            return [];
        }

        $data = [];
        for ($row = 2; $row <= $highestRow; ++$row) {
            foreach ($columns as $key => $value) {
                $data[$row - 2][$value] = $worksheet->getCellByColumnAndRow($key + 1, $row)->getValue();
            }
        }
        return $data;
    }
}